from flask import jsonify
from database.connectorMysq import DatabaseUtils
from model.tools import generateUserToken, compareUserTokens
from config.config import mysqlConfig

db_utils = DatabaseUtils(
    pool_name='user',
    host=mysqlConfig["host"],
    user=mysqlConfig["user"],
    password=mysqlConfig["password"],
    database=mysqlConfig["database"]
)


def user_reg(userid, password, username):
    try:
        with db_utils.get_connection() as connection:
            userToken = generateUserToken(userid)
            query = "INSERT INTO user (userid, password, username, token, grade) VALUES (%s, %s, %s, %s, '1')"
            data = (userid, password, username, userToken)

            with connection.cursor() as cursor:
                cursor.execute(query, data)
                connection.commit()

            result = {
                "msg": "注册成功",
                "token": userToken
            }
            return result
    except Exception as e:
        print(e)
        result = {
            "msg": "注册失败，请稍后重试",
            "token": None
        }
        return result


def user_login(userid, password):
    try:
        with db_utils.get_connection() as connection:
            userToken = generateUserToken(userid)
            query = "SELECT username, password, grade,bg FROM user WHERE userid = '{}'".format(userid)
            with connection.cursor() as cursor:
                cursor.execute(query)
                data = cursor.fetchall()
            if len(data) == 0:
                result = {
                    "msg": "登录失败，用户名错误",
                    "token": None
                }
                return result
            if password == data[0][1]:
                query = "UPDATE user SET token = '{}' WHERE userid = '{}'".format(userToken, userid)
                with connection.cursor() as cursor:
                    cursor.execute(query)
                    connection.commit()
                result = {
                    "msg": "登录成功",
                    "username": data[0][0],
                    "token": userToken,
                    "grade": data[0][2],
                    "background": data[0][3]
                }
                return result
            else:
                result = {
                    "msg": "登录失败，密码错误",
                    "token": None
                }
                return result
    except Exception as e:
        print(e)
        result = {
            "msg": "登录失败，发生异常",
            "token": None
        }
        return result


def user_add_background(userid, token, background):
    print(token)
    try:
        with db_utils.get_connection() as connection:
            if not compareUserTokens(connection, userid, token):
                return {"msg": "token已经过期，请重新获取"}

            query = "UPDATE user SET bg = %s WHERE userid = %s"

            with connection.cursor() as cursor:
                cursor.execute(query, (background, userid))
                connection.commit()

            result = {
                "msg": "设置背景成功"
            }
            return result
    except Exception as e:
        print(e)
        result = {
            "msg": "设置背景失败，请稍后重试"
        }
        return result


def addUserCollect(userid, url, img, navname, token):
    try:
        with db_utils.get_connection() as connection:
            if not compareUserTokens(connection, userid, token):
                return {"msg": "token已经过期，请重新获取"}

            query = "INSERT INTO collect (userid, url, img, navname) VALUES (%s, %s, %s, %s)"
            data = (userid, url, img, navname)

            with connection.cursor() as cursor:
                cursor.execute(query, data)
                connection.commit()

            result = {
                "msg": "增加成功"
            }
            return result
    except Exception as e:
        print(e)
        result = {
            "msg": "增加失败，请稍后重试"
        }
        return result


def getUserCollect(userid, token):
    try:
        with db_utils.get_connection() as connection:
            if not compareUserTokens(connection, userid, token):
                return {"msg": "token已经过期，请重新获取"}

            query = "SELECT url, img, navname, id FROM collect WHERE userid = %s"
            data = (userid,)

            with connection.cursor() as cursor:
                cursor.execute(query, data)
                data = cursor.fetchall()

            jsons = [{
                "id": item[3],
                "name": item[2],
                "img": item[1],
                "url": item[0]
            } for item in data]

            return jsonify(jsons)
    except Exception as e:
        print(e)
        return "获取失败"


def delUserCollect(userid, navid, token):
    try:
        with db_utils.get_connection() as connection:
            if not compareUserTokens(connection, userid, token):
                return {"msg": "token已经过期，请重新获取"}

            query = "DELETE FROM collect WHERE id = %s"
            data = (navid,)

            with connection.cursor() as cursor:
                cursor.execute(query, data)
                connection.commit()

            return "删除成功"
    except Exception as e:
        print(e)
        return "删除未成功"


def getCategoryNavlist(category):
    try:
        with db_utils.get_connection() as connection:
            query = "SELECT * FROM navlist WHERE category = %s"
            data = (category,)

            with connection.cursor() as cursor:
                cursor.execute(query, data)
                data = cursor.fetchall()

            jsons = [{
                "id": item[0],
                "name": item[2],
                "img": item[4],
                "url": item[3]
            } for item in data]

            return jsonify(jsons)
    except Exception as e:
        print(e)
        return "获取失败"
